package org.kjk.board;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import org.kjk.oracleDB.ConnectionPool;

public class BoardDAOImpl implements BoardDAO {
	ConnectionPool connectionPool;
	Connection conn;

	public BoardDAOImpl() {
		try {
			connectionPool = ConnectionPool.getInstance();
			conn = connectionPool.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	public <T> int insert(T obj) {
		PreparedStatement pstmt = null;
		String sql = "insert into board(SERIAL, SUBJECT, CONTENT, WRITER) values((select max(serial) + 1 from board), ?, ?, ?)";
		int res = 0;
		try {
			BoardDTO dto = (BoardDTO) obj;
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, dto.getSubject());
			pstmt.setString(2, dto.getContent());
			pstmt.setString(3, dto.getWriter());
			res = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		connectionPool.close(null, pstmt, null);
		return res;
	}

	@Override
	public <T> int update(T obj) {
		PreparedStatement pstmt = null;
		String sql = "update board set SUBJECT=?, CONTENT=?, WRITER=? where SERIAL=?";
		int res = 0;
		try {
			pstmt = conn.prepareStatement(sql);
			BoardDTO dto = (BoardDTO) obj;
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, dto.getSubject());
			pstmt.setString(2, dto.getContent());
			pstmt.setString(3, dto.getWriter());
			pstmt.setInt(4, dto.getSerial());
			res = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		connectionPool.close(null, pstmt, null);
		return res;
	}

	@Override
	public <T> int delete(T obj) {
		PreparedStatement pstmt = null;
		String sql = "delete from board where SERIAL=?";
		int res = 0;
		try {
			pstmt = conn.prepareStatement(sql);
			BoardDTO dto = (BoardDTO) obj;
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, dto.getSerial());
			res = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		connectionPool.close(null, pstmt, null);
		return res;
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	@Override
	public List select(String sql, String[] param) {
		List<BoardDTO> result = new ArrayList<BoardDTO>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = conn.prepareStatement(sql);
			if (param != null) {
				for (int i = 0; i < param.length; i++) {
					pstmt.setString(i + 1, param[i]);
				}
			}
			rs = pstmt.executeQuery();
			while (rs.next()) {
				int serial = rs.getInt("SERIAL");
				String subject = rs.getString("SUBJECT");
				String content = rs.getString("CONTENT");
				String writer = rs.getString("WRITER");
				String writtenDate = rs.getString("WRITTENDATE");
				BoardDTO bean = new BoardDTO(serial, subject, content, writer, writtenDate);
				result.add(bean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		connectionPool.close(rs, pstmt, conn);
		return result;
	}

	@Override
	public JSONObject selectJson(String sql, String[] param)
			throws JSONException {
		JSONObject json = new JSONObject();
		JSONArray array = new JSONArray();
		json.put("result", array);
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = conn.prepareStatement(sql);
			if (param != null) {
				for (int i = 0; i < param.length; i++) {
					pstmt.setString(i + 1, param[i]);
				}
			}
			rs = pstmt.executeQuery();
			while (rs.next()) {
				int serial = rs.getInt("SERIAL");
				String subject = rs.getString("SUBJECT");
				String content = rs.getString("CONTENT");
				String writer = rs.getString("WRITER");
				String writtenDate = rs.getString("WRITTENDATE");
				JSONObject obj1 = new JSONObject();
				obj1.put("serial", serial);
				obj1.put("subject", subject);
				obj1.put("content", content);
				obj1.put("writer", writer);
				obj1.put("writtenDate", writtenDate);
				array.put(obj1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		connectionPool.close(rs, pstmt, conn);
		return json;
	}

	@Override
	protected void finalize() throws Throwable {
		super.finalize();
		connectionPool.close(null, null, conn);
	}

}
